﻿using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Easy.Common;

namespace Easy.DAL
{
    public class SiteMapDAL
    {
        private readonly SqlDatabase sqldb = new SqlDatabase(Config.TulingBuyCrmConnectString);

        public DataTable GetSiteMapByUserId(Guid UserId, int ClientType)
        {
            string sqlCommand = string.Format(@"
                select a.SiteMapId,a.Name,a.Url,a.Icon,a.Parent,a.ClientType,max(case when b.UserRoleRelationshipId IS Null then 0 else 1 end) Privilege 
                from SiteMap as a 
                left join SiteMapRoleMapRelationship as c on a.SiteMapId=c.SiteMapId 
                left join UserRoleRelationship as b on b.RoleMapId=c.RoleMapId and b.SystemUserId=@UserId 
                where a.ClientType = @ClientType
                and a.DeletionStateCode = 0
                group by a.SiteMapId,a.Name,a.Url,a.Icon,a.Parent,a.Sort,a.ClientType order by a.Sort");
            DbCommand cmd = sqldb.GetSqlStringCommand(sqlCommand);
            SqlParameter[] param = { 
                                       new SqlParameter("@UserId", UserId),
                                       new SqlParameter("@ClientType", ClientType) 
                                   };
            cmd.Parameters.AddRange(param);
            DataTable dt = sqldb.ExecuteDataSet(cmd).Tables[0];
            return dt;
        }
        public DataTable GetSiteMapByRoleId(Guid RoleId)
        {
            string sqlCommand = string.Format(@"
                select a.SiteMapId,a.Name,a.Url,a.Icon,a.Parent,a.ClientType,(case when b.SiteMapRoleMapRelationshipId IS Null then 0 else 1 end) Privilege 
                from SiteMap as a 
                left join SiteMapRoleMapRelationship as b on a.SiteMapId=b.SiteMapId and b.RoleMapId=@RoleId
                where a.DeletionStateCode = 0
                order by a.Sort");
            DbCommand cmd = sqldb.GetSqlStringCommand(sqlCommand);
            SqlParameter[] param = { new SqlParameter("@RoleId", RoleId) };
            cmd.Parameters.AddRange(param);
            DataTable dt = sqldb.ExecuteDataSet(cmd).Tables[0];
            return dt;
        }
    }
}
